﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace DAO
{
    public class NhanVienDAO
    {
        NHANVIEN nvi ;
        public int KiemTraLogin(string p1, string p2)
        {
             QLThuVienDataContext dt = new QLThuVienDataContext();
             var nvInfo = dt.NHANVIENs.Where(nv => nv.TAI_KHOAN == p1 && nv.MAT_KHAU == p2).SingleOrDefault();
             if (nvInfo != null)
             {
                 return 1;
             }
             else {
                 return 2;
             }
        }



        public NHANVIEN LayThongTinNhanVien(string p1, string p2)
        {
            
            QLThuVienDataContext dt = new QLThuVienDataContext();
           // nvi = dt.NHANVIENs.Where(nv => nv.TAI_KHOAN == p1 && nv.MAT_KHAU == p2);
            var nvInfo = dt.NHANVIENs.Where(nv => nv.TAI_KHOAN == p1 && nv.MAT_KHAU == p2).SingleOrDefault();
            nvi = new NHANVIEN();
           
            nvi.MA_NHAN_VIEN = nvInfo.MA_NHAN_VIEN;
            nvi.TEN_NHAN_VIEN = nvInfo.TEN_NHAN_VIEN;
            nvi.DIA_CHI = nvInfo.DIA_CHI;
            nvi.NGAY_SINH = nvInfo.NGAY_SINH;
            nvi.SDT = nvInfo.SDT;
            nvi.LOAI_NHAN_VIEN = nvInfo.LOAI_NHAN_VIEN;
            nvi.TAI_KHOAN = nvInfo.TAI_KHOAN;
            nvi.MAT_KHAU = nvInfo.MAT_KHAU.Trim();
       
            return nvi;
        }

        public NHANVIEN LayThongTinNhanVienBangID(int ID)
        {

            QLThuVienDataContext dt = new QLThuVienDataContext();
            // nvi = dt.NHANVIENs.Where(nv => nv.TAI_KHOAN == p1 && nv.MAT_KHAU == p2);
            var nvInfo = dt.NHANVIENs.Where(nv => nv.MA_NHAN_VIEN == ID ).SingleOrDefault();
            nvi = new NHANVIEN();
            
            nvi.MA_NHAN_VIEN = nvInfo.MA_NHAN_VIEN;
            nvi.TEN_NHAN_VIEN = nvInfo.TEN_NHAN_VIEN;
            nvi.DIA_CHI = nvInfo.DIA_CHI;
            nvi.NGAY_SINH = nvInfo.NGAY_SINH;
            nvi.SDT = nvInfo.SDT;
            nvi.LOAI_NHAN_VIEN = nvInfo.LOAI_NHAN_VIEN;
            nvi.TAI_KHOAN = nvInfo.TAI_KHOAN;
            nvi.MAT_KHAU = nvInfo.MAT_KHAU.Trim();

            return nvi;
        }
        public Boolean CapNhatMatKhau(NHANVIEN nv, string mkm)
        {
            QLThuVienDataContext db = new QLThuVienDataContext();
            var acc = (from nvs in db.NHANVIENs where nvs.MA_NHAN_VIEN == nv.MA_NHAN_VIEN select nvs).SingleOrDefault();
            if (acc == null)
            {
                return false;
            }
            acc.MAT_KHAU = mkm;
            db.SubmitChanges();
            return true;
            
            

             
        }



       
        public IQueryable<object> LayDSNhanVien()
        {
            QLThuVienDataContext db = new QLThuVienDataContext();
            // var dsNhanVien = (from nv in db.NHANVIENs select nv).ToList();
            var dsNhanVien = (from nv in db.NHANVIENs select new { nv.MA_NHAN_VIEN, nv.TEN_NHAN_VIEN, nv.SDT, nv.DIA_CHI, nv.NGAY_SINH, nv.LOAI_NHAN_VIEN, nv.TAI_KHOAN, nv.MAT_KHAU }).AsQueryable(); 
            return dsNhanVien;
            // return db.NHANVIENs.ToList();
        }

        public object LayLoaiNhanVien()
        {
            QLThuVienDataContext db = new QLThuVienDataContext();
            return db.LOAINHANVIENs;
        }

        public bool KiemTraTaiKhoanTrung(string p)
        {
            QLThuVienDataContext db = new QLThuVienDataContext();
            var tk = (from nv in db.NHANVIENs where nv.TAI_KHOAN.Trim() == p.Trim() select nv).SingleOrDefault();

           if(tk == null){
               var tk1 = (from dg in db.DOCGIAs where dg.MA_DOC_GIA.Trim() == p.Trim() select dg).SingleOrDefault();
               if (tk1 == null)
               {
                   return false;
               }
               else {
                   return true;
               }
               return false;
           }
            return true;
        }

        public string ThemNhanVien(NHANVIEN nv)
        {
            QLThuVienDataContext db = new QLThuVienDataContext();
            
            db.NHANVIENs.InsertOnSubmit(nv);
            try
            {
                db.SubmitChanges();
            }
            catch (Exception ex){
                return ex.Message;
            }

            return "";
        }
        public string themphieumuon(string ma, DateTime ngay)
        {
           QLThuVienDataContext db = new QLThuVienDataContext();
            PHIEUMUON p = new PHIEUMUON();
            p.MA_DOC_GIA = ma;
            p.NGAY_MUON = ngay;
            db.PHIEUMUONs.InsertOnSubmit(p);
            try
            {
                db.SubmitChanges();
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

            return "";

            }
        public string themchitietphieumuon(int maphieu, int masach)
        {
            QLThuVienDataContext db = new QLThuVienDataContext();
            CHITIETPHIEUMUON p = new CHITIETPHIEUMUON();
            p.MA_PHIEU_MUON = maphieu;
            p.MA_SACH = masach;
            db.CHITIETPHIEUMUONs.InsertOnSubmit(p);
            try
            {
                db.SubmitChanges();
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

            return "";

        }
        public string capnhatphieu(int ma, DateTime ngay, string madg)
        {

            QLThuVienDataContext db = new QLThuVienDataContext();
            var phieu = (from n in db.PHIEUMUONs where n.MA_PHIEU == ma select n).SingleOrDefault();
     
            if (phieu == null)
            {
                return "Cập Nhật Thất Bại";
            }
            phieu.MA_DOC_GIA = madg;
            phieu.NGAY_MUON = ngay;
            try
            {
                db.SubmitChanges();
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

            return "";

        }
        public string XoaPhieu(int ma)
        {
            QLThuVienDataContext db = new QLThuVienDataContext();
            var chitiet = (from a in db.CHITIETPHIEUMUONs
                           where a.MA_PHIEU_MUON == ma
                           select a).ToList();
            db.CHITIETPHIEUMUONs.DeleteAllOnSubmit(chitiet);
            var getData = (from nvi in db.PHIEUMUONs
                           where nvi.MA_PHIEU == ma
                           select nvi).Single();

            db.PHIEUMUONs.DeleteOnSubmit(getData);
            try
            {
                db.SubmitChanges();
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

            return "";
        }
        public string XoaPhieuMuon(int ma)
        {
            QLThuVienDataContext db = new QLThuVienDataContext();
            var chitiet = (from a in db.CHITIETPHIEUMUONs
                           where a.MA_CHI_TIET == ma
                           select a).SingleOrDefault();
            db.CHITIETPHIEUMUONs.DeleteOnSubmit(chitiet);

            try
            {
                db.SubmitChanges();
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

            return "";
        }
        public string CapNhatNhanVien(NHANVIEN nv)
        {
            QLThuVienDataContext db = new QLThuVienDataContext();
            var nvi = (from n in db.NHANVIENs where n.MA_NHAN_VIEN == nv.MA_NHAN_VIEN select n).SingleOrDefault();
            
            if (nvi == null)
            {
                return "Cap nhat that bai.";
            }
            nvi.MA_NHAN_VIEN = nv.MA_NHAN_VIEN;
            nvi.TEN_NHAN_VIEN = nv.TEN_NHAN_VIEN;
            nvi.DIA_CHI = nv.DIA_CHI;
            nvi.NGAY_SINH = nv.NGAY_SINH;
            nvi.SDT = nv.SDT;
            nvi.LOAI_NHAN_VIEN = nv.LOAI_NHAN_VIEN;
            nvi.TAI_KHOAN = nv.TAI_KHOAN;
            nvi.MAT_KHAU = nv.MAT_KHAU.Trim();
            try
            {
                db.SubmitChanges();
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

            return "";
        }

        public bool IsNumber(string pText)
        {
            Regex regex = new Regex(@"^[-+]?[0-9]*\.?[0-9]+$");
            return regex.IsMatch(pText);
        }
        public bool KiemTraTaiKhoanTrung2(NHANVIEN nv)
        {
            QLThuVienDataContext db = new QLThuVienDataContext();
           
                var count = db.NHANVIENs.Count(me => me.TAI_KHOAN == nv.TAI_KHOAN && me.MA_NHAN_VIEN == nv.MA_NHAN_VIEN);
                if (count == 1)
                {
                    return false;
                }
                else
                {
                    var count1 = db.NHANVIENs.Count(me => me.TAI_KHOAN == nv.TAI_KHOAN);
                    if (count1 == 0)
                    {
                        return false;
                    }
                    else
                    {
                        return true;
                    }
                }
            
           
               


            return true;
        }

        public string XoaNhanVien(NHANVIEN nv)
        {
            QLThuVienDataContext db = new QLThuVienDataContext();
            var getData = (from nvi in db.NHANVIENs
                           where nvi.MA_NHAN_VIEN == nv.MA_NHAN_VIEN
                           select nvi).Single();

            db.NHANVIENs.DeleteOnSubmit(getData);
            try
            {
                db.SubmitChanges();
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

            return "";
        }

        public object LayDanhSachDocGia()
        {
            QLThuVienDataContext db = new QLThuVienDataContext();
            // var dsNhanVien = (from nv in db.NHANVIENs select nv).ToList();
            var dsNhanVien = (from nv in db.DOCGIAs select new { nv.MA_DOC_GIA, nv.TEN_DOC_GIA, nv.DIA_CHI,nv.SDT ,nv.NGAY_SINH, nv.LOAI_DOC_GIA, nv.NGUOI_LAP,nv.NGAY_LAP_THE,nv.NGAY_HET_HAN,nv.MAT_KHAU }).AsQueryable(); ;
            return dsNhanVien;
        }

        public object LayLoaiDocGia()
        {
            QLThuVienDataContext db = new QLThuVienDataContext();
            return db.LOAIDOCGIAs;
        }

        public string ThemDocGia(DOCGIA dg)
        {
            QLThuVienDataContext db = new QLThuVienDataContext();

            db.DOCGIAs.InsertOnSubmit(dg);
            try
            {
                db.SubmitChanges();
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

            return "";
        }

        public string XoaDocGia(DOCGIA dg)
        {
            QLThuVienDataContext db = new QLThuVienDataContext();
            var getData = (from dgi in db.DOCGIAs
                           where dgi.MA_DOC_GIA == dg.MA_DOC_GIA
                           select dgi).Single();

            db.DOCGIAs.DeleteOnSubmit(getData);
            try
            {
                db.SubmitChanges();
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

            return "";
        }

        public string ChinhSuaThongTinDG(DOCGIA dg)
        {
            QLThuVienDataContext db = new QLThuVienDataContext();
            var dgi = (from d in db.DOCGIAs where d.MA_DOC_GIA.Trim() == dg.MA_DOC_GIA.Trim() select d).SingleOrDefault();

            if (dgi == null)
            {
                return "Cập nhật thất bại.";
            }
           
            dgi.TEN_DOC_GIA = dg.TEN_DOC_GIA;
            dgi.DIA_CHI = dg.DIA_CHI;
            dgi.NGAY_SINH = dg.NGAY_SINH;
            dgi.SDT = dg.SDT;
            dgi.LOAI_DOC_GIA = dg.LOAI_DOC_GIA;
            dgi.NGAY_LAP_THE = dg.NGAY_LAP_THE;
            dgi.NGAY_HET_HAN = dg.NGAY_HET_HAN;
            dgi.NGUOI_LAP = dg.NGUOI_LAP;
            dgi.MAT_KHAU = dg.MAT_KHAU;
            try
            {
                db.SubmitChanges();
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

            return "";
        }

        public string themchitietphieutra(int maphieu, int masach)
        {
            QLThuVienDataContext db = new QLThuVienDataContext();
            CHITIETPHIEUTRA p = new CHITIETPHIEUTRA();
            p.MA_PHIEU_TRA = maphieu;
            p.MA_SACH = masach;
            db.CHITIETPHIEUTRAs.InsertOnSubmit(p);
            try
            {
                db.SubmitChanges();
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

            return "";
        }

     
        public string ThemPhieuTra(string madg, DateTime ngaymuon, string tienphat)
        {
            QLThuVienDataContext db = new QLThuVienDataContext();
            PHIEUTRA p = new PHIEUTRA();
            p.MA_DOC_GIA = madg;
            p.NGAY_MUON = ngaymuon;
            p.TIEN_PHAT = int.Parse(tienphat);
            db.PHIEUTRAs.InsertOnSubmit(p);
            try
            {
                db.SubmitChanges();
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

            return "";
        }

        public string XoaCTPhieuTra(int ma)
        {
            QLThuVienDataContext db = new QLThuVienDataContext();
            var chitiet = (from a in db.CHITIETPHIEUTRAs
                           where a.MA_CHI_TIET == ma
                           select a).SingleOrDefault();
            db.CHITIETPHIEUTRAs.DeleteOnSubmit(chitiet);

            try
            {
                db.SubmitChanges();
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

            return "";
        }

        public string capnhatphieutra(int ma, DateTime ngay, string madg, string tienphat)
        {
            QLThuVienDataContext db = new QLThuVienDataContext();
            var phieu = (from n in db.PHIEUTRAs where n.MA_PHIEU == ma select n).SingleOrDefault();

            if (phieu == null)
            {
                return "Cập Nhật Thất Bại";
            }
            phieu.MA_DOC_GIA = madg;
            phieu.NGAY_MUON = ngay;
            phieu.TIEN_PHAT = int.Parse(tienphat);
            try
            {
                db.SubmitChanges();
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

            return "";
        }

        public string XoaPhieuTra(int ma)
        {
            QLThuVienDataContext db = new QLThuVienDataContext();
            var chitiet = (from a in db.CHITIETPHIEUTRAs
                           where a.MA_PHIEU_TRA == ma
                           select a).ToList();
            db.CHITIETPHIEUTRAs.DeleteAllOnSubmit(chitiet);
            var getData = (from nvi in db.PHIEUTRAs
                           where nvi.MA_PHIEU == ma
                           select nvi).Single();

            db.PHIEUTRAs.DeleteOnSubmit(getData);
            try
            {
                db.SubmitChanges();
            }
            catch (Exception ex)
            {
                return ex.Message;
            }

            return "";
        }

        public string ThemPhieuTraBangPhieuMuon(string maphieu)
        {
            QLThuVienDataContext db = new QLThuVienDataContext();
            var phieu = (from pt in db.PHIEUMUONs where pt.MA_PHIEU == int.Parse(maphieu) select pt).SingleOrDefault();
            PHIEUTRA p = new PHIEUTRA();
            p.MA_DOC_GIA = phieu.MA_DOC_GIA;
            p.NGAY_MUON = phieu.NGAY_MUON;
            p.TIEN_PHAT = 0;
            db.PHIEUTRAs.InsertOnSubmit(p);
            try
            {
                db.SubmitChanges();
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
            List<CHITIETPHIEUMUON> ds = new List<CHITIETPHIEUMUON>();
            CHITIETPHIEUTRA ctpt = new CHITIETPHIEUTRA();
            var ctphieu = (from pt in db.CHITIETPHIEUMUONs where pt.MA_PHIEU_MUON == int.Parse(maphieu) select pt).ToList();
            ds= ctphieu;
            int mattmax = db.PHIEUTRAs.Max(row => row.MA_PHIEU);
            int mactmax = db.CHITIETPHIEUTRAs.Max(row => row.MA_CHI_TIET) +1;
            for(int i=0; i<= ds.Count-1;i++)
            {
                ctpt = new CHITIETPHIEUTRA();
                ctpt.MA_CHI_TIET = mactmax;
                 ctpt.MA_SACH = ds[i].MA_SACH;
                 ctpt.MA_PHIEU_TRA = mattmax;
                 db.CHITIETPHIEUTRAs.InsertOnSubmit(ctpt);
                 try
                 {
                     db.SubmitChanges();
                 }
                 catch (Exception ex)
                 {
                     return ex.Message;
                 }
            }
            

            return "";
        }

        public bool kiemtraphieumuon(string maphieu)
        {
            QLThuVienDataContext db = new QLThuVienDataContext();
            var phieu = (from p in db.PHIEUMUONs where p.MA_PHIEU == int.Parse(maphieu) select p).SingleOrDefault();

            if (phieu == null)
            {
                return false;
            }
            return true;
        }
    }
}
